package com.gitee.melin.bee.core.jdbc.dialect.sqlserver;

import com.gitee.melin.bee.core.jdbc.dialect.AbstractDBQuery;

import java.sql.ResultSet;
import java.sql.SQLException;

public class SqlServerQuery extends AbstractDBQuery {

    private static final String QUERY_TABLE_BY_SCHEMA_SQL = "SELECT a.NAME TABLE_NAME,a.create_date 'CREATE_TIME',a.modify_date 'UPDATE_TIME'," +
            "SCHEMA_NAME (schema_id) TABLE_SCHEMA,b.rows AS 'ROWS',8 * cast(dpages as bigint) * 1024 'DATA_LENGTH',c.VALUE 'COMMENTS' " +
            "FROM sys.TABLES a LEFT JOIN sysindexes b ON a.object_id=b.id AND indid IN (0,1) LEFT JOIN sys.extended_properties c " +
            "ON a.object_id=c.major_id AND c.NAME='MS_Description' AND c.minor_id=0 WHERE a.type='U' AND SCHEMA_NAME (schema_id)='%s'";

    private static final String QUERY_COLUMNS_SQL = " SELECT  cast(a.name AS VARCHAR(500)) AS TABLE_NAME,cast(b.name AS VARCHAR(500)) AS COLUMN_NAME,  isnull(CAST ( c.VALUE AS NVARCHAR ( 500 ) ),'') AS COMMENTS, "
            + " CASE b.is_nullable WHEN 1 THEN 'YES' ELSE 'NO' END as NULLVALUE,cast(sys.types.name AS VARCHAR (500)) AS DATA_TYPE,"
            + " ( SELECT CASE count(1) WHEN 1 then 'PRI' ELSE '' END FROM syscolumns,sysobjects,sysindexes,sysindexkeys,systypes  WHERE syscolumns.xusertype = systypes.xusertype "
            + " AND syscolumns.id =  sc.id AND sysobjects.xtype = 'PK' AND sysobjects.parent_obj = syscolumns.id  "
            + " AND sysindexes.id = syscolumns.id  AND sysobjects.name = sysindexes.name AND sysindexkeys.id = syscolumns.id  AND sysindexkeys.indid = sysindexes.indid  "
            + "AND syscolumns.colid = sysindexkeys.colid "
            + " AND syscolumns.name = b.name) as 'KEY',  b.is_identity isIdentity , '' as CHARACTER_SET_NAME, '' as COLLATION_NAME, "
            + "0 as ORDINAL_POSITION,sc.prec as LENGTH, b.PRECISION as NUMERIC_PRECISION, b.scale as NUMERIC_SCALE,"
            + "'' as AUTO_INCREMENT "
            + "FROM ( select name,object_id,schema_id from sys.tables UNION all select name,object_id,schema_id from sys.views ) a  INNER JOIN sys.columns b "
            + " ON b.object_id = a.object_id  INNER JOIN syscolumns sc on sc.id = b.object_id and sc.name = b.name INNER JOIN sys.schemas s on a.schema_id = s.schema_id  LEFT JOIN sys.types ON b.user_type_id = sys.types.user_type_id    LEFT JOIN sys.extended_properties c ON c.major_id = b.object_id "
            + "AND c.minor_id = b.column_id  WHERE a.name = '%s' and s.name = '%s' and sys.types.name !='sysname' ";

    @Override
    public String tablesSql(String schemaName) {
        return String.format(QUERY_TABLE_BY_SCHEMA_SQL, schemaName);
    }

    @Override
    public String columnsSql(String schemaName, String tableName) {
        return String.format(QUERY_COLUMNS_SQL, tableName, schemaName);
    }

    @Override
    public String schemaName() {
        return "TABLE_SCHEMA";
    }

    @Override
    public String tableName() {
        return "TABLE_NAME";
    }

    @Override
    public String tableType() {
        return "TYPE";
    }

    @Override
    public String tableComment() {
        return "COMMENTS";
    }

    @Override
    public String columnName() {
        return "COLUMN_NAME";
    }

    @Override
    public String columnType() {
        return "DATA_TYPE";
    }

    @Override
    public String columnComment() {
        return "COMMENTS";
    }

    @Override
    public String columnKey() {
        return "KEY";
    }

    public boolean isKeyIdentity(ResultSet results) throws SQLException {
        return 1 == results.getInt("isIdentity");
    }

    public String isNullable() {
        return "NULLVALUE";
    }
}
